<?php
require str_replace('test', 'src', dirname(__FILE__)) . '/Maker.php';
class SQL_MakerTest extends PHPUnit_Framework_TestCase
{
    private $sql_maker;

    public function setUp()
    {
        $pdo_mysql = new PDO('mysql:dbname=test;host=localhost', 'root', 1);
        $this->sql_maker['mysql'][] = new SQL_Maker('mysql', $pdo_mysql, array('table_name'=>'user'));
        $mysqli = new mysqli('localhost', 'root', '1', 'test');
        $this->sql_maker['mysql'][] = new SQL_Maker('mysql', $mysqli, array(
                'table_name'=>'user',
                'api' => 'mysqli',
            )
        );

        $pdo_pgsql = new PDO('pgsql:host=localhost;port=5432;dbname=test;user=root;password=1');
        $this->sql_maker['pgsql'][] = new SQL_Maker('pgsql', $pdo_pgsql, array(
                'schema_name' => 'schema1',
                'table_name'=>'user',
            )
        );
        $pgsql = pg_connect('host=localhost port=5432 dbname=test user=root password=1');
        $this->sql_maker['pgsql'][] = new SQL_Maker('pgsql', $pgsql, array(
                'api' => 'pgsql',
                'schema_name' => 'schema1',
                'table_name'=>'user',
            )
        );

    }

    public function testDelete()
    {
        $where = array(
            'id' => '123',
            'name' => 'jim',
        );
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->delete($where);
            var_dump($r);
            $this->assertEquals("DELETE FROM `user` WHERE `id` = '123' AND `name` = 'jim'", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $r = $one->delete($where);
            var_dump($r);
            $this->assertEquals('DELETE FROM "schema1"."user" WHERE "id" = \'123\' AND "name" = \'jim\'', $r);
        }
    }
    
    public function testInsertRow()
    {
        $data = array(
            'id' => '123',
            'name' => 'jim',
        );
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->insertRow($data);
            var_dump($r);
            $this->assertEquals("INSERT INTO `user` (`id`,`name`) VALUES ('123','jim')", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $r = $one->insertRow($data);
            var_dump($r);
            $this->assertEquals('INSERT INTO "schema1"."user" ("id","name") VALUES (\'123\',\'jim\')', $r);
        }
    }

    public function testInsertRows()
    {
        $data = array(
            array(
                'id' => '123',
                'name' => 'jim',
            ),
            array(
                'id' => '2',
                'name' => 'lucy',
            ),
        );
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->insertRows($data);
            var_dump($r);
            $this->assertEquals("INSERT INTO `user` (`id`,`name`) VALUES ('123','jim'),('2','lucy')", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $r = $one->insertRows($data);
            var_dump($r);
            var_dump($r);
            $this->assertEquals('INSERT INTO "schema1"."user" ("id","name") VALUES (\'123\',\'jim\'),(\'2\',\'lucy\')', $r);
        }
    }

    public function testSelect()
    {
        $where = array(
            'id' => '123',
            'name' => 'jim',
        );
        $column = array('id', 'name');
        $limit_start = 1;
        $limit_size = 3;
        $group_by = 'id';
        $order_by = 'id desc';
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->select($where, $column, $limit_start, $limit_size, $group_by, $order_by);
            var_dump($r);
            $this->assertEquals("SELECT `id`,`name` FROM `user` WHERE `id` = '123' AND `name` = 'jim' GROUP BY `id` ORDER BY `id` DESC LIMIT 1,3", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $group_by = array('id', 'name'); //pgsql where must equal group_by
            $r = $one->select($where, $column, $limit_start, $limit_size, $group_by, $order_by);
            var_dump($r);
            $this->assertEquals('SELECT "id","name" FROM "schema1"."user" WHERE "id" = \'123\' AND "name" = \'jim\' GROUP BY "id","name" ORDER BY "id" DESC LIMIT 3 OFFSET 1', $r);
        }
    }

    public function testSelectCount()
    {
        $where = array(
            'id' => '123',
            'name' => 'jim',
        );
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->selectCount($where);
            var_dump($r);
            $this->assertEquals("SELECT COUNT(*) FROM `user` WHERE `id` = '123' AND `name` = 'jim'", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $r = $one->selectCount($where);
            var_dump($r);
            $this->assertEquals('SELECT COUNT(*) FROM "schema1"."user" WHERE "id" = \'123\' AND "name" = \'jim\'', $r);
        }
    }
    
    public function testUpdate()
    {
        $data = array(
            'id' => '123',
            'name' => 'jim\'s lucy',
        );
        $where = array(
            'id' => '123',
            'name' => 'jim',
        );
        foreach($this->sql_maker['mysql'] as $one) {
            $r = $one->update($data, $where);
            var_dump($r);
            $this->assertEquals("UPDATE `user` SET `id`='123',`name`='jim\'s lucy' WHERE `id` = '123' AND `name` = 'jim'", $r);
        }
        foreach($this->sql_maker['pgsql'] as $one) {
            $r = $one->update($data, $where);
            var_dump($r);
            $this->assertEquals('UPDATE "schema1"."user" SET "id"=\'123\',"name"=\'jim\'\'s lucy\' WHERE "id" = \'123\' AND "name" = \'jim\'', $r);
        }
    }
}
?>
